package com.iflytek.jcy.supervise.datastore.api;

import javax.sql.DataSource;
import java.io.Closeable;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.hutool.core.util.StrUtil;
import com.iflytek.jcy.supervise.datastore.dto.FieldInfo;
import com.iflytek.jcy.supervise.datastore.dto.TableInfo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @author <a href=mailto:ktyi@iflytek.com>伊开堂</a>
 * @date 2022/10/12
 */
public class DataSourceHolder<T extends DataSource> {
    protected final Logger logger = LoggerFactory.getLogger(DataSourceHolder.class);

    private T dataSource;

    public DataSourceHolder(T dataSource) {
        this.dataSource = dataSource;
    }

    public void close() {
        if (dataSource != null && dataSource instanceof Closeable) {
            try {
                ((Closeable) dataSource).close();
            }
            catch (Exception e) {
                logger.warn("数据库连接池关闭异常", e);
            }
        }
    }
    public T getDataSource() {
        return dataSource;
    }

    public List<TableInfo> readAllViews() {
        return readTables("VIEW");

    }
    public List<TableInfo> readAllTables() {
        return readTables("TABLE");
    }
    private List<TableInfo> readTables(String type) {
        List<TableInfo> tableInfoList = new ArrayList<>();
        ResultSet rs = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            DatabaseMetaData metaData = conn.getMetaData();
            /*
             * "%" 表示任意
             * catalog - 数据库名称；为 null 表示所有
             * schemaPattern - 模式名称；为 null 表示所有
             * tableNamePattern - 表名称
             * types - 表类型，"TABLE"、"VIEW"、"SYSTEM TABLE"、"GLOBAL TEMPORARY"、"LOCAL TEMPORARY"、"ALIAS" 和 "SYNONYM"；
             * null 表示返回所有类型
             */
            String catalog = conn.getCatalog();
            String schema = conn.getSchema();
            rs = metaData.getTables(catalog, schema, "%", new String[]{type});
            while (rs.next()) {
                TableInfo tableInfo = parseTableInfo(rs);
                if (tableInfo != null) {
                    List<FieldInfo> fieldInfos = parseFieldInfo(conn, catalog, schema, tableInfo.getTableName());
                    tableInfo.setFieldInfos(fieldInfos);
                    tableInfoList.add(tableInfo);
                }
            }
        }
        catch (Exception e) {
            logger.warn("获取所有表名异常", e);
        }
        finally {
            close(rs);
            close(conn);
        }
        return tableInfoList;
    }

    private List<FieldInfo> parseFieldInfo(Connection conn, String catalog, String schema, String tablename)
            throws SQLException {
        List<FieldInfo> fieldInfos = new ArrayList<>();

        ResultSet rs = conn.getMetaData().getColumns(catalog, schema, tablename, "%");
        while (rs.next()) {
            String tableCat = readStr(rs, "TABLE_CAT");
            String tableSchem = readStr(rs, "TABLE_SCHEM");
            String tableName = readStr(rs, "TABLE_NAME");
            String columnName = readStr(rs, "COLUMN_NAME");
            int dataType = readInt(rs, "DATA_TYPE");
            String typeName = readStr(rs, "TYPE_NAME");
            int columnSize = readInt(rs, "COLUMN_SIZE");
            int decimalDigits = readInt(rs, "DECIMAL_DIGITS");
            int numPrecRadix = readInt(rs, "NUM_PREC_RADIX");
            int nullable = readInt(rs, "NULLABLE");
            String remarks = readStr(rs, "REMARKS");
            String columnDef = readStr(rs, "COLUMN_DEF");
            int charOctetLength = readInt(rs, "CHAR_OCTET_LENGTH");
            int ordinalPosition = readInt(rs, "ORDINAL_POSITION");
            String isNullable = readStr(rs, "IS_NULLABLE");
            // String scopeCatlog = readStr(rs, "SCOPE_CATLOG");
            // String scopeSchema = readStr(rs, "SCOPE_SCHEMA");
            // String scopeTable = readStr(rs, "SCOPE_TABLE");
            // short sourceDataType = readShort(rs, "SOURCE_DATA_TYPE");
            String isAutoincrement = readStr(rs, "IS_AUTOINCREMENT");

            FieldInfo info = new FieldInfo();
            info.setTableCat(tableCat);
            info.setTableSchem(tableSchem);
            info.setTableName(tableName);
            info.setColumnName(columnName);
            if (StrUtil.isBlank(columnName)) {
                continue;
            }

            info.setDataType(dataType);
            info.setTypeName(typeName);
            info.setColumnSize(columnSize);
            info.setDecimalDigits(decimalDigits);
            info.setNumPrecRadix(numPrecRadix);
            info.setNullable(nullable);
            info.setRemarks(remarks);
            info.setColumnDef(columnDef);
            info.setCharOctetLength(charOctetLength);
            info.setOrdinalPosition(ordinalPosition);
            info.setIsNullable(isNullable);
            // info.setScopeCatlog(scopeCatlog);
            // info.setScopeSchema(scopeSchema);
            // info.setScopeTable(scopeTable);
            // info.setSourceDataType(sourceDataType);
            info.setIsAutoincrement(isAutoincrement);
            fieldInfos.add(info);
        }

        return fieldInfos;
    }

    private TableInfo parseTableInfo(ResultSet rs) {
        String tableCat = readStr(rs, "TABLE_CAT");
        String tableSchem = readStr(rs, "TABLE_SCHEM");
        String tableName = readStr(rs, "TABLE_NAME");
        String tableType = readStr(rs, "TABLE_TYPE");
        String tableRemarks = readStr(rs, "REMARKS");
        String typeCat = readStr(rs, "TYPE_CAT");
        String typeSchem = readStr(rs, "TYPE_SCHEM");
        String tableTypeName = readStr(rs, "TYPE_NAME");
        String selfReferencingColName = readStr(rs, "SELF_REFERENCING_COL_NAME");
        String refGeneration = readStr(rs, "REF_GENERATION");
        if (StrUtil.isBlank(tableName)) {
            return null;
        }

        TableInfo tableInfo = new TableInfo();
        tableInfo.setTableCat(tableCat);
        tableInfo.setTableSchem(tableSchem);
        tableInfo.setTableName(tableName);
        tableInfo.setTableType(tableType);
        tableInfo.setRemarks(tableRemarks);
        tableInfo.setTypeCat(typeCat);
        tableInfo.setTypeSchem(typeSchem);
        tableInfo.setTableTypeName(tableTypeName);
        tableInfo.setSelfReferencingColName(selfReferencingColName);
        tableInfo.setRefGeneration(refGeneration);
        return tableInfo;
    }

    private short readShort(ResultSet rs, String columnLabel) {
        try {
            return rs.getShort(columnLabel);
        }
        catch (Exception e) {
            logger.warn("读取ResultSet结果字段异常: {}", e.getMessage());
        }
        return 0;
    }

    private int readInt(ResultSet rs, String DATA_TYPE) {
        try {
            return rs.getInt(DATA_TYPE);
        }
        catch (Exception e) {
            logger.warn("读取ResultSet结果字段异常: {}", e.getMessage());
        }
        return 0;
    }

    private String readStr(ResultSet rs, String columnLabel) {
        try {
            return rs.getString(columnLabel);
        }
        catch (Exception e) {
            logger.warn("读取ResultSet结果字段异常: {}", e.getMessage());
            return null;
        }
    }

    private void close(PreparedStatement pStemt) {
        if (pStemt != null) {
            try {
                pStemt.close();
            }
            catch (Exception e) {
                logger.warn("关闭PreparedStatement异常", e);
            }
        }
    }

    private void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            }
            catch (SQLException e) {
                logger.error("close connection failure", e);
            }
        }
    }

    private void close(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            }
            catch (Exception e) {
                logger.error("close ResultSet failure", e);
            }
        }
    }
}
